﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace Koeltp.DataAccess
{
    /// <summary>
    ///MSSQLDAL 的摘要说明
    /// </summary>
    public class MSSQLDAL : IDataAccess
    {
        //private SqlConnection connection;
        private SqlTransaction transaction;
        private string connStr;
        public MSSQLDAL(string connStr)
        {
            this.connStr = connStr;
        }
        /// <summary>
        /// 添删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, CommandType commandType, params QueryParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(null,connection, cmd, commandType, sql, parameters);
                int state;
                try
                {
                    state = cmd.ExecuteNonQuery();
                }
                catch
                {
                    state = 0;
                }
                finally
                {
                    cmd.Parameters.Clear();
                }
                return state;
            }
        }
        /// <summary>
        /// 得到单个查询结果 一般用于执行sql中的Count(*)、Sum(price)等
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public object GetScalar(string sql, CommandType commandType, params QueryParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(null,connection, cmd, commandType, sql, parameters);
                object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return obj;
            }
        }
        /// <summary>
        /// 得到一个表的数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="dt"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataTable GetTable(string sql, DataTable dt, CommandType commandType, params QueryParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(null,connection, cmd, commandType, sql, parameters);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                cmd.Parameters.Clear();
                return dt;
            }

        }

        public IDataReader GetReader(string sql, CommandType commandType, params QueryParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(null,connection, cmd, commandType, sql, parameters);
                SqlDataReader dr = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return dr;
            }

        }
        public string DeleteTran(string sql, params QueryParameter[] paras)
        {
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(null, connection, cmd, CommandType.StoredProcedure, sql, paras);
                cmd.Parameters.Add("@return", "").Direction = ParameterDirection.ReturnValue;
                cmd.ExecuteNonQuery();
                string value = cmd.Parameters["@return"].Value.ToString();
                return value;
            }

        }
        /// <summary>
        /// 文章列表分页
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public DataSet ArticleListPaging(string sql, QueryParameter[] paras)
        {
            using (SqlConnection connection = new SqlConnection(connStr))
            {

                SqlCommand cmd = new SqlCommand();
                PrepareCommand(null, connection, cmd, CommandType.StoredProcedure, sql, paras);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                try
                {
                    da.Fill(ds);
                }
                catch (Exception)
                {
                }
                finally
                {
                    if (da != null)
                        da.Dispose();
                    if (cmd != null)
                        cmd.Dispose();
                }
                return ds;
            }

        }
        /// <summary>
        /// 为执行命令准备参数
        /// </summary>
        /// <param name="transaction">数据库事物处理</param>
        /// <param name="connection">已经存在的数据库连接</param>
        /// <param name="cmd">SqlCommand命令</param>
        /// <param name="commandType">SqlCommand命令类型（存储过程，T-SQL语句，等等）</param>
        /// <param name="commandText">存储过程的名字或者T-SQL语句</param>
        /// <param name="commandParameter">返回带参数的命令</param>
        private void PrepareCommand(SqlTransaction transaction, SqlConnection connection, SqlCommand cmd, CommandType commandType, string commandText, params QueryParameter[] commandParameter)
        {
            if (connection.State == ConnectionState.Closed && connection != null)
            {
                connection.Open();
            }
            if (transaction != null)
            {
                cmd.Transaction = transaction;
            }
            cmd.Connection = connection;
            cmd.CommandType = commandType;
            cmd.CommandText = commandText;
            
            
            if (commandParameter != null && commandParameter.Length > 0)
            {
                for (int i = 0; i < commandParameter.Length; i++)
                {
                    if (commandParameter[i] != null)
                    {
                        cmd.Parameters.AddWithValue(commandParameter[i].Name, commandParameter[i].Value);
                    }
                }
            }
        }
    }
}